import tkinter as tk
from tkinter import scrolledtext
import pymssql


def open(s):
    window = tk.Tk()
    window.title("系统管理人员(增加)-" + s)
    window.geometry("500x300")
    # 设置列的权重
    window.grid_columnconfigure(0, weight=1)
    window.grid_columnconfigure(1, weight=2)
    window.grid_columnconfigure(2, weight=1)

    label1 = tk.Label(window, text="植物名称：")
    entry1 = tk.Entry(window)
    label2 = tk.Label(window, text="科：")
    entry2 = tk.Entry(window)
    label3 = tk.Label(window, text="属：")
    entry3 = tk.Entry(window)
    label4 = tk.Label(window, text="种：")
    entry4 = tk.Entry(window)
    label5 = tk.Label(window, text="植物别名：")
    entry5 = tk.Entry(window)
    label6 = tk.Label(window, text="植物生长环境：")
    entry6 = tk.Entry(window)
    label7 = tk.Label(window, text="植物所属省份：")
    entry7 = tk.Entry(window)

    conn = pymssql.connect(host='localhost', server='LAPTOP-QBHKFR9T', port='1433', user='Mysql',
                           password='15681743536',
                           database='植物管理系统', charset='utf8')

    if conn:
        print("连接成功！")

    def check_credentials1():
        p_name = entry1.get()
        p_name2 = entry7.get()
        p_name3 = entry3.get()
        p_name4 = entry4.get()
        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT 植物编号 FROM 基本信息 WHERE  植物名称 = '{p_name}'"
        cursor.execute(sql)
        result1 = " "
        for row in cursor:  # 对每一行数据遍历输出
            result1 = row[0]

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT path FROM 分类 WHERE  名称 = '{p_name4}'"
        cursor.execute(sql)
        result2 = " "
        for row in cursor:  # 对每一行数据遍历输出
            numbers = row[0].split('/')
            result2 += "%s" % ('/'.join(numbers))
            result2 = result2 + "/" + result1
        cursor = conn.cursor()  # 获取游标
        sql = f"INSERT INTO 分类(编号,名称,path) VALUES('{result1}','{p_name}','{result2}') "
        cursor.execute(sql)
        conn.commit()  # 提交事务，使更改生效

        scrollbar.insert(tk.INSERT,
                         "植物：" + p_name + "ID：" + result1 + "path:" + result2 + "的科:" + p_name2 + "属:" + p_name3 + "种:" + p_name4 + "信息添加完成！")

    def check_credentials2():
        p_name = entry1.get()
        # p_name = entry5.get()
        p_envir = entry6.get()
        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT 植物编号 FROM 基本信息 WHERE  植物名称 = '{p_name}'"
        cursor.execute(sql)
        result1 = " "
        for row in cursor:  # 对每一行数据遍历输出
            result1 = row[0]

        cursor = conn.cursor()  # 获取游标
        sql = f"INSERT INTO 生长环境(植物编号,生长环境) VALUES('{result1}','{p_envir}') "
        cursor.execute(sql)
        conn.commit()  # 提交事务，使更改生效

        scrollbar.insert(tk.INSERT, "植物名称：" + p_name + "生长环境：" + p_envir + "信息添加完成！")

    def check_credentials3():
        p_name = entry1.get()
        County = entry7.get()
        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT 植物编号 FROM 基本信息 WHERE  植物名称 = '{p_name}'"
        cursor.execute(sql)
        result3 = " "
        for row in cursor:  # 对每一行数据遍历输出
            result3 = row[0]
        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT max(编号) as Id FROM 分布 "
        cursor.execute(sql)
        result1 = " "
        for row in cursor:  # 对每一行数据遍历输出
            result1 = row[0] + 1
            print(result1)
        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT 县编号 FROM region WHERE  分布区域 = '{County}'"
        cursor.execute(sql)
        result2 = " "
        for row in cursor:  # 对每一行数据遍历输出
            result2 = row[0]
            print(result2)
        cursor = conn.cursor()  # 获取游标
        sql = f"INSERT INTO 分布(编号,植物编号,县编号) VALUES('{result1}','{result3}','{result2}') "
        cursor.execute(sql)
        conn.commit()  # 提交事务，使更改生效

        scrollbar.insert(tk.INSERT,
                         "植物ID：" + str(p_name) + "省份：" + str(County) + "信息添加完成！")

    button1 = tk.Button(window, text="添加科属种", command=lambda: check_credentials1(), width=20, height=1)
    button2 = tk.Button(window, text="添加生长环境", command=lambda: check_credentials2(), width=20,
                        height=1)  # 得到科，属，种，生长环境，分布区域
    button3 = tk.Button(window, text="添加分布区域", command=lambda: check_credentials3(), width=20,
                        height=1)  # 仅查询得到植物情况即可

    back_button = tk.Button(window, text="返回上一级", command=window.destroy, width=20, height=1)

    label1.grid(row=0, column=0)
    entry1.grid(row=0, column=1)
    label2.grid(row=0, column=2)
    entry2.grid(row=0, column=3)
    label3.grid(row=1, column=0)
    entry3.grid(row=1, column=1)
    label4.grid(row=1, column=2)
    entry4.grid(row=1, column=3)
    label5.grid(row=2, column=0)
    entry5.grid(row=2, column=1)
    label6.grid(row=2, column=2)
    entry6.grid(row=2, column=3)
    label7.grid(row=3, column=0)
    entry7.grid(row=3, column=1)

    button1.grid(row=4, column=1)
    button2.grid(row=4, column=2)
    button3.grid(row=5, column=1)

    back_button.grid(row=5, column=2)
    scrollbar = scrolledtext.ScrolledText(window, wrap=tk.WORD, width=60, height=10)
    scrollbar.grid(row=6, column=0, columnspan=4, sticky="nsew")
    window.mainloop()
    pass
